package com.xiaowu.commons.utils;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  
/** 
 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息，注明出处！ 
 *  
 * @author leno 
 * @version v1.0 
 * @param <T> 
 *            应用泛型，代表任意一个符合javabean风格的类 
 *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx() 
 *            byte[]表jpg格式的图片数据 
 */  
public class ExportExcel<T>  
{  
    
    
    public HSSFWorkbook exportExcelByList(String title,String[][] headers,List<?> dataset, String pattern){
    	// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		
		//设置标题
		for (short i = 0; i < headers.length; i++) {
			row.createCell(i).setCellValue(headers[i][0]);
		}
		for (int i = 0; i < dataset.size(); i++) {
			row = sheet.createRow(i+1);  
			for (int j = 0; j < headers.length; j++) {
				HSSFCell cell = row.createCell(j);
				Object object = ReflectionUtils.getFieldValue(dataset.get(i),headers[j][1]);
				//时间格式化
				if (object instanceof Date){
					 Date date = (Date) object;  
                     SimpleDateFormat sdf = new SimpleDateFormat(pattern);  
                     object = sdf.format(date);  
				}else if(object == null){
        			object = "-";
        		}
				cell.setCellValue( object.toString()) ;
			}
		}
		return workbook;
		
    }
    
    /**
     * 传入list实体对象 生成xlsx文件
     * @param title
     * @param headers
     * @param dataset
     * @param pattern
     * @return
     */
    public SXSSFWorkbook  exportExcelSXSSByListObject(String title,String[][] headers,List<?> dataset, String pattern){
    	// 声明一个工作薄
    	SXSSFWorkbook  workbook=new SXSSFWorkbook ();
		// 生成一个表格
		Sheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 产生表格标题行
		Row row = sheet.createRow(0);
		
		//设置标题
		for (short i = 0; i < headers.length; i++) {
			row.createCell(i).setCellValue(headers[i][0]);
		}
		for (int i = 0; i < dataset.size(); i++) {
			row = sheet.createRow(i+1);  
			for (int j = 0; j < headers.length; j++) {
				Cell cell = row.createCell(j);
				Object object = ReflectionUtils.getFieldValue(dataset.get(i),headers[j][1]);
				//时间格式化
				if (object instanceof Date){
					 Date date = (Date) object;  
                     SimpleDateFormat sdf = new SimpleDateFormat(pattern);  
                     object = sdf.format(date);  
				}else if(object == null){
        			object = "-";
        		}
				cell.setCellValue( object.toString()) ;
			}
		}
		return workbook;
		
    }
    
    public void exportExcelSXSSByListObject(String title,String[][] headers,List<?> dataset, String pattern,HttpServletResponse response) throws IOException{
    	SXSSFWorkbook workbook = exportExcelSXSSByListObject(title, headers, dataset, pattern);
    	response.setContentType("applicatoin/octet-stream");
		response.setHeader("Content-disposition","attachment; filename="+new String((title).getBytes("gbk"),"iso8859-1")+".xlsx");
		workbook.write(response.getOutputStream());
    }
    
  
    /**
     * 传入list字符串 生成xlsx文件
     * @param title
     * @param headers
     * @param dataset
     * @param out
     * @param pattern
     * @return
     */
    public SXSSFWorkbook exportExcelSXSSByListString(String title, String[] headers,

    	List<List<Object>> dataset) {
		// 声明一个工作薄
    	SXSSFWorkbook workbook = new SXSSFWorkbook();
    	
		// 生成一个表格
		Sheet sheet = workbook.createSheet(title);
		
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 产生表格标题行
		Row row = sheet.createRow(0);

		for (short i = 0; i < headers.length; i++) {
			row.createCell(i).setCellValue(headers[i]);
		}
		int index = 0;
		for (int i = 0; i < dataset.size(); i++) {
			index++;
			row = sheet.createRow(index);

			List<Object> list = dataset.get(i);
			for (int j = 0; j < list.size(); j++) {
				String content = "-";
				if (list.get(j) != null) {
					content = list.get(j) + "";
				}
				row.createCell(j).setCellValue(content);
			}
		}
		return workbook;
    }
    
  
    public static void main(String[] args)  
    {  
    	
        /*// 测试学生  
        ExportExcel<Student> ex = new ExportExcel<Student>();  
        String[] headers =  
        { "学号", "姓名", "年龄", "性别", "出生日期" };  
        List<Student> dataset = new ArrayList<Student>();  
        dataset.add(new Student(10000001, "张三", 20, true, new Date()));  
        dataset.add(new Student(20000002, "李四", 24, false, new Date()));  
        dataset.add(new Student(30000003, "王五", 22, true, new Date()));  
        // 测试图书  
        ExportExcel<Book> ex2 = new ExportExcel<Book>();  
        String[] headers2 =  
        { "图书编号", "图书名称", "图书作者", "图书价格", "图书ISBN", "图书出版社", "封面图片" };  
        List<Book> dataset2 = new ArrayList<Book>();  
        try  
        {  
            BufferedInputStream bis = new BufferedInputStream(  
                    new FileInputStream("V://book.bmp"));  
            byte[] buf = new byte[bis.available()];  
            while ((bis.read(buf)) != -1)  
            {  
                //  
            }  
            dataset2.add(new Book(1, "jsp", "leno", 300.33f, "1234567",  
                    "清华出版社", buf));  
            dataset2.add(new Book(2, "java编程思想", "brucl", 300.33f, "1234567",  
                    "阳光出版社", buf));  
            dataset2.add(new Book(3, "DOM艺术", "lenotang", 300.33f, "1234567",  
                    "清华出版社", buf));  
            dataset2.add(new Book(4, "c++经典", "leno", 400.33f, "1234567",  
                    "清华出版社", buf));  
            dataset2.add(new Book(5, "c#入门", "leno", 300.33f, "1234567",  
                    "汤春秀出版社", buf));  
  
            OutputStream out = new FileOutputStream("E://a.xls");  
            OutputStream out2 = new FileOutputStream("E://b.xls");  
            ex.exportExcel(headers, dataset, out);  
            ex2.exportExcel(headers2, dataset2, out2);  
            out.close();  
            out2.close();  
            JOptionPane.showMessageDialog(null, "导出成功!");  
            System.out.println("excel导出成功！");  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  */
    }  
}  